﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace scmsByAspdNet.cotro.teacher {
  /// <summary>
  /// getStudentScoreByCT 的摘要说明
  /// </summary>
  public class getStudentScoreByCT : IHttpHandler {

    public void ProcessRequest(HttpContext context) {
      context.Response.ContentType = "text/plain";

      //获取数据
      string tea_id = context.Request["tea_id"];
      string cour_id = context.Request["cour_id"];

      //获得
      SqlConnection conn = new SqlConnection("Data Source=106.54.69.143;database=scms;uid=sa;pwd=Aspdotnet666");
      conn.Open();
      //获取行
      SqlCommand sql_rows = new SqlCommand("SELECT ss.id,ss.name,scs.daily_grade,scs.terminal_grade,scs.ultimate_grade,scs.submit_flag,sc.proportion" +
        " FROM sc_teacher st, sc_course sc, sc_cour_tea sct, sc_cour_stu scs,sc_student ss" +
        " WHERE st.id='" + tea_id + "' AND" +
        " sc.id='" + cour_id + "' AND" +
        " sct.cour_id=sc.id AND" +
        " sct.tea_id = st.id AND" +
        " sct.cour_id=scs.cour_id AND" +
        " scs.stu_id=ss.id", conn);
      string rows = "[";
      SqlDataReader reader = sql_rows.ExecuteReader();
      for (; reader.Read();) {
        rows += $"{{\"stu_id\":\"{reader[0].ToString()}\",\"stu_name\":\"{reader[1].ToString()}\",\"daily_grade\":\"{reader[2].ToString()}\",\"terminal_grade\":\"{reader[3].ToString()}\",\"ultimate_grade\":\"{reader[4].ToString()}\",\"submit_flag\":\"{reader[5].ToString()}\",\"proportion\":\"{reader[6].ToString()}\"}},";
      }
      rows = rows.Substring(0, rows.Length - 1);
      rows += "]";
      reader.Close();
      //获取行数-----
      SqlCommand sql_total = new SqlCommand("SELECT count(ss.id)" +
        " FROM sc_teacher st, sc_course sc, sc_cour_tea sct, sc_cour_stu scs,sc_student ss" +
        " WHERE st.id='" + tea_id + "' AND" +
        " sc.id='" + cour_id + "' AND" +
        " sct.cour_id=sc.id AND" +
        " sct.tea_id=st.id AND" +
        " sct.cour_id=scs.cour_id AND" +
        " scs.stu_id=ss.id", conn);
      string total = "";
      reader = sql_total.ExecuteReader();
      if (reader.Read()) {
        total = reader[0].ToString();
      }
      else {
        total = "0";
      }
      reader.Close();
      conn.Close();

      //返回数据
      string ret = "{" +
        "\"total\":\"" + total + "\"," +
        "\"rows\":" + rows + "" +
        "}";
      context.Response.StatusCode = 200;
      context.Response.Write(ret);
    }

    public bool IsReusable {
      get {
        return false;
      }
    }
  }
}